﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using VideoClassLibrary;
using VideoClassLibrary.Model;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class CourselistDestModule
    {
        Database db = VDataBase.CreateDatabase();
        string sql = "";

       
        /// <summary>
        /// 加分配
        /// </summary>
        /// <param name="V_courselistDest"></param>
        public void AddCourselistDest(string schoolID, string userID, string trainClassID)
        {
            string[] userIdArray = userID.Split(',');
            StringBuilder strSql = new StringBuilder();
            StringBuilder oneSql = new StringBuilder();
            oneSql.Append(" If (Select COUNT(1) from V_courselistDest where trainClassid=@trainClassid and  UserID=@UserID and SchoolID=@SchoolID ) =0  begin  ");
            oneSql.Append(" INSERT INTO [V_courselistDest]([trainClassid],[UserID],[SchoolID]) VALUES(@trainClassid,@UserID,@SchoolID) ");
            oneSql.Append("   end ; ");
            for (int i = 0; i < userIdArray.Length; i++)
            {
                strSql.Append(oneSql.ToString().Replace("@trainClassid", trainClassID).Replace("@UserID", userIdArray[i]).Replace("@SchoolID", schoolID));
            }
            try
            {
                db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
            }
            catch
            {
            }

        }
        public void DeleCourselistDest(V_courselistDest V_courselistDest)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_courselistDest where");
            strSql.Append(" SchoolID='" + V_courselistDest.SchoolID.ToString() + "'");
            strSql.Append(" and trainClassid='" + V_courselistDest.TrainClassId.ToString() + "'");
            strSql.Append(" and userid='" + V_courselistDest.UserID.ToString() + "'");
            db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
        }
        #region
       
        /// <summary>
        /// 搜索课程分配
        /// </summary>
        /// <param name="SchoolID"></param>
        /// <param name="courseID"></param>
        /// <param name="VU_loginname"></param>
        /// <param name="VU_realname"></param>
        /// <returns></returns>
        public DataSet searchCourselistDest(string SchoolID, string courseID, string VU_loginname, string VU_realname)
        {
            DataSet ds;
            
            sql = "select ";
            sql += " courselistID,UserID,VC_name,VT_realname,courseID,VU_realname,VU_loginname,VCL_startDate,vtc_name";
            sql += " from  ";
            sql += " V_courselist join V_room on V_room.id = V_courselist.roomid  ";
            sql += " join V_teacher on V_teacher.id = V_courselist.teacherID  ";
            sql += " join V_course on V_course.id = V_courselist.courseID , ";
            sql += " V_courselistDest join V_user on V_courselistDest.UserID=V_user.ID";
            sql += " where  V_courselist.ID=V_courselistDest.courselistID ";
            sql += " and V_courselist.SchoolID = '" + SchoolID + "'";

            if (courseID != "")
            {
                sql += " and V_courselist.courseID = '" + courseID + "'";
            }

            if (VU_loginname != "")
            {
                sql += " and V_user.VU_loginname = '" + VU_loginname + "'";
            }

            if (VU_realname != "")
            {
                sql += " and V_user.VU_realname = '" + VU_realname + "'";
            }
            
            

            ds = db.ExecuteDataSet(CommandType.Text, sql);

            return ds;
        }
        #endregion

        public DataSet QueryCourselistDest(int schoolid, int trainId, string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select vu_realname,vu_loginname, vtc_name,vtc_starDate,vtc_endDate,vc.trainclassid,vc.userid,VTC_Number ");
            strSql.Append(" from (select distinct userid,schoolid,trainclassid from V_courselistDest where schoolid=" + schoolid + ") as vc ");
            strSql.Append(" join v_trainClass on v_trainClass.id=vc.trainclassid");
            strSql.Append(" join v_user on v_user.id=vc.userid where 1=1 and vc.trainclassid =" + trainId);
            if (sqlWhere != "")
            {
                strSql.Append(sqlWhere);

            }

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            return ds;
        }

        public DataSet QueryCourselistDest(int schoolid, string sqlWhere)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select vu_realname,vu_loginname, vtc_name,vtc_starDate,vtc_endDate,vc.trainclassid,vc.userid ,VTC_Number");
            strSql.Append(" from (select distinct userid,schoolid,trainclassid from V_courselistDest where schoolid="+schoolid+") as vc ");
            strSql.Append(" join v_trainClass on v_trainClass.id=vc.trainclassid");
            strSql.Append(" join v_user on v_user.id=vc.userid where 1=1 ");
            if (sqlWhere != "")
            {
                strSql.Append(sqlWhere);

            }

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            return ds;
        }

       /// <summary>
        /// 根据所属学校查询培训班内容
       /// </summary>
       /// <param name="schoolId"></param>
       /// <param name="trainingClassId"></param>
       /// <returns></returns>
        public DataSet QueryBySchoolIdAll(int schoolId, string trainingClassId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select vu_realname,vu_loginname,vtc_name,vtc_starDate,vtc_endDate ,vc.trainclassid ,vc.userid,VTC_Number");
            sqlStr.Append(" from (select distinct userid,schoolid,trainclassid,id from V_courselistDest where schoolid="+schoolId+") as vc ");
            sqlStr.Append(" join v_user on v_user.id =vc.userid join v_trainClass on v_trainClass.id=vc.trainclassid");
            if (trainingClassId == null)
            {

                sqlStr.Append("");

            }
            else {

                sqlStr.Append(" where trainclassid='" + trainingClassId + "'");
            }
           sqlStr.Append(" order by vc.id desc");
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlStr.ToString());
            return ds;
        }



        /// <summary>
        /// 根据所在学校查询培训班名称
        /// </summary>
        /// <param name="SchoolID"></param>
        /// <returns></returns>
        public DataSet CreateCourseDs(string SchoolID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ID,vtc_name,VTC_Number from v_trainClass where  SchoolID='" + SchoolID + "'");
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 获取用户所在培训班下面的所有课程日期
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<DateTime> GetUserAllCourseDate(int schoolId, int userId)
        {
            List<DateTime> dtList = new List<DateTime>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT  distinct(b.VCL_startDate)  FROM V_courselistDest  a join  V_courselist b on a.TrainClassID=b.trainClassID  where a.UserID=" + userId);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables[0].Rows[i];
                    dtList.Add(DateTime.Parse(dr["VCL_startDate"].ToString()));
                }
            }

            return dtList;
        }
    }
}
